package xju.rjxy.shop.mapper.impl;

import xju.rjxy.shop.mapper.ReviewMapper;
import xju.rjxy.shop.model.domain.Review;
import xju.rjxy.shop.model.vo.ReviewVO;
import xju.rjxy.shop.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.math.BigDecimal;
import java.util.List;

/**
 * 评论数据访问接口实现类
 */
public class ReviewMapperImpl implements ReviewMapper {
    private final QueryRunner runner = new QueryRunner();

    /**
     * 查询评论列表
     */
    @Override
    public List<ReviewVO> selectReviewList() {
        // 修正表名t_review为reviews，并完善字段
        String sql = "SELECT " +
                "id, " +
                "user_id AS userId, " +
                "game_id AS gameId, " +
                "rating, " +
                "comment, " +
                "create_time AS createTime, " +
                "flag, " +
                "status_code AS statusCode, " +
                "deleted " +
                "FROM reviews " +
                "WHERE flag = 2 AND deleted = 0"; // 过滤已通过且未删除的评论

        List<ReviewVO> reviewList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            reviewList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(ReviewVO.class)
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return reviewList;
    }
    
    /**
     * 根据游戏ID查询评论列表
     */
    @Override
    public List<ReviewVO> selectByGameId(Integer gameId) {
        String sql = "SELECT " +
                "r.id, " +
                "r.user_id AS userId, " +
                "r.game_id AS gameId, " +
                "r.rating, " +
                "r.comment, " +
                "r.create_time AS createTime, " +
                "r.flag, " +
                "r.status_code AS statusCode, " +
                "r.deleted, " +
                "u.username, " +
                "u.avatar AS userAvatar " +
                "FROM reviews r " +
                "JOIN users u ON r.user_id = u.id " +
                "WHERE r.game_id = ? AND r.flag = 2 AND r.deleted = 0 AND u.deleted = 0 " +
                "ORDER BY r.create_time DESC";

        List<ReviewVO> reviewList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            reviewList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(ReviewVO.class),
                    gameId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return reviewList;
    }
    
    /**
     * 根据用户ID查询评论列表
     */
    @Override
    public List<ReviewVO> selectByUserId(Integer userId) {
        String sql = "SELECT " +
                "r.id, " +
                "r.user_id AS userId, " +
                "r.game_id AS gameId, " +
                "r.rating, " +
                "r.comment, " +
                "r.create_time AS createTime, " +
                "r.flag, " +
                "r.status_code AS statusCode, " +
                "r.deleted, " +
                "u.username, " +
                "u.avatar AS userAvatar " +
                "FROM reviews r " +
                "JOIN users u ON r.user_id = u.id " +
                "WHERE r.user_id = ? AND r.flag = 2 AND r.deleted = 0 AND u.deleted = 0 " +
                "ORDER BY r.create_time DESC";

        List<ReviewVO> reviewList = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            reviewList = runner.query(
                    connection,
                    sql,
                    new BeanListHandler<>(ReviewVO.class),
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return reviewList;
    }
    
    /**
     * 添加评论
     */
    @Override
    public int insertReview(Review review) {
        String sql = "INSERT INTO reviews (user_id, game_id, rating, comment, create_time, flag, status_code, deleted) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    review.getUserId(), 
                    review.getGameId(), 
                    review.getRating(), 
                    review.getComment(), 
                    review.getCreateTime(), 
                    review.getFlag(), 
                    review.getStatusCode(), 
                    review.getDeleted());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 更新评论
     */
    @Override
    public int updateReview(Review review) {
        String sql = "UPDATE reviews SET rating = ?, comment = ?, flag = ?, status_code = ? WHERE id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, 
                    review.getRating(), 
                    review.getComment(), 
                    review.getFlag(), 
                    review.getStatusCode(), 
                    review.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 删除评论
     */
    @Override
    public int deleteReview(Integer id) {
        String sql = "UPDATE reviews SET deleted = 1 WHERE id = ?";
        int result = 0;
        try {
            Connection connection = JDBCUtils.getConnection();
            result = runner.update(connection, sql, id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return result;
    }
    
    /**
     * 根据游戏ID获取平均评分
     */
    @Override
    public Double getAverageRatingByGameId(Integer gameId) {
        String sql = "SELECT AVG(rating) FROM reviews WHERE game_id = ? AND flag = 2 AND deleted = 0";
        Double averageRating = null;
        try {
            Connection connection = JDBCUtils.getConnection();
            Object result = runner.query(connection, sql, new ScalarHandler<>(), gameId);
            if (result instanceof BigDecimal) {
                averageRating = ((BigDecimal) result).doubleValue();
            } else if (result instanceof Number) {
                averageRating = ((Number) result).doubleValue();
            } else {
                averageRating = 0.0;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return averageRating;
    }
} 